# -*- coding: utf-8 -*-
from __future__ import print_function, division
#系统库
#import datetime
#import decimal
#import time
#第三方库
import MySQLdb
import MySQLdb.cursors
#import MySQLdb
#import MySQLdb.cursors
#自定义库
import local


#主体
def auto_reconnect(f):
    def func(self, *args, **kwargs):
        try:
            return f(self, *args, **kwargs)
        except MySQLdb.OperationalError, e:
            if e[0] == 2006:  # (2006, 'MySQL server has gone away')
                # _Log.warning(e, exc_info=True)
                self.__init__()  # reconnect
                return func(self, *args, **kwargs)
            else:
                raise e
            return
    #func.__doc__ = f.__doc__
    return func


class Database:
    def __init__(self):
        self.connection = MySQLdb.connect(host="localhost", port=3306, charset="utf8", db="tspweb", cursorclass=MySQLdb.cursors.DictCursor)

    def __del__(self):
        self.connection.close()

    @auto_reconnect
    def register(self, username, password, email):
        code = 0
        errormsg = ""
        try:
            cursor = self.connection.cursor()
            cursor.execute("INSERT INTO accounts(username, userpassword , email ) VALUES (%s, %s, %s)", (username, password, email))
            self.connection.commit()
        except Exception, e:
            code = e[0]
            if e[0] == 1062:
                index = e[1].find("email", 0)
                if index == -1:
                    errormsg = local.String.RG_ER_usernameregistered
                else:
                    errormsg = local.String.RG_ER_emailregistered
            elif e[0] == 0:
                errormsg = ""
            else:
                errormsg = e[1]
        finally:
            cursor.close()
        return code, errormsg

    @auto_reconnect
    def login(self, username, password):
        code = 0
        errormsg = ""
        userinfo = None
        cursor = self.connection.cursor()
        cursor.execute("SELECT * FROM accounts WHERE username = %s", (username))
        ret = cursor.fetchall()
        if len(ret) != 1:
            code = 10001
            errormsg = local.String.LG_ER_usernameerror
        else:
            if ret[0]['userpassword'] != password:
                code = 10002
                errormsg = local.String.LG_ER_passwderror
            else:
                userinfo = ret[0]
        return userinfo, code, errormsg

"""
    @auto_reconnect
    def check(self,username,password):
        cursor = self.connection.cursor();
        cursor.execute("SELECT userpassword FROM accounts WHERE username = %s", (username));
        pw = cursor.fetchall();
        cursor.close();
        if len(pw) <1 :
            return False;
        if cmp(pw[0][0],password) != 0:
            return False;
        else:
            return True;

#----------------------------------------------------------------------------------------
    @auto_reconnect
    def get_answer_to_question(self , q_id):
        #获取相关问题的答案
        cursor = self.connection.cursor()
        cursor.execute("SELECT * FROM answers WHERE q_id = %s" , (q_id));
        answers = list(cursor.fetchall())
        answers.sort(lambda x,y: cmp(x['a_label'], y['a_label']));
        answers = sorted(answers, key=lambda x:x['a_label']);
        a = [];
        for item in answers:
            temp = answer()
            temp.id = item['a_id'];
            temp.label = item['a_label'];
            temp.text = item['a_text'];
            temp.input = item['a_input'];
            a.append(temp);
        cursor.close();
        return a

    @auto_reconnect
    def get_question(self , q_id):
        #获取问题
        cursor = self.connection.cursor()
        cursor.execute("SELECT * FROM questions WHERE q_id = %s" , (q_id));
        qs = cursor.fetchall()
        if len(qs) != 1:
            return
        q = question();
        q.title = qs[0]['q_title'];
        q.type = qs[0]['q_type'];
        q.id = q_id;
        cursor.close();
        q.answers = self.get_answer_to_question(q_id);
        for item in q.answers:
            if item.input:
                q.has_input = 1
                return q
        return q;

    @auto_reconnect
    def get_questions_to_list(self , list_id):
        #获取相关问题的答案
        cursor = self.connection.cursor()
        cursor.execute("SELECT * FROM question_in_list WHERE list_id = %s" , (list_id));
        questions = cursor.fetchall()
        qs = [];
        for i in range(len(questions)):
            item = questions[i];
            qid = item['q_id']
            q = self.get_question(qid);
            q.order = i + 1 ;
            qs.append(q)
        cursor.close();
        return qs;

    @auto_reconnect
    def get_survey_by_sid(self , s_id):
        #获取问卷表
        cursor = self.connection.cursor()
        cursor.execute("SELECT * FROM surveys WHERE s_id = %s" , (s_id));
        list = cursor.fetchall()
        l = question_list()
        l.id=list[0]['s_id'];
        l.title = list[0]['s_title']
        l.description = list[0]['s_desc']
        l.running = list[0]['s_running']
        l.has_r = list[0]['s_answer']
        cursor.close();
        l.questions = self.get_questions_to_list(s_id)
        return l;

    @auto_reconnect
    def get_survey(self , au_id):
        #获取问卷表
        cursor = self.connection.cursor()
        cursor.execute("SELECT * FROM appuser WHERE au_id = %s" , (au_id));
        user = cursor.fetchall()
        s_id = user[0]["s_id"];
        if s_id == 0 :
            return None;
        cursor.execute("SELECT * FROM surveys WHERE s_id = %s" , (s_id));
        list = cursor.fetchall()
        l = question_list()
        l.id=list[0]['s_id'];
        l.title = list[0]['s_title']
        l.description = list[0]['s_desc']
        cursor.close();
        l.questions = self.get_questions_to_list(s_id)
        return l;

    @auto_reconnect
    def save_answer(self , answer , q_id):
        #向数据库存入一个答案
        cursor = self.connection.cursor()
        cursor.execute("INSERT INTO answers(q_id, a_label , a_text , a_input) VALUES (%s, %s, %s , %s)", (q_id , answer.label , answer.text , answer.input));
        cursor.close();
        return;

    @auto_reconnect
    def save_question( self , q ):
        cursor = self.connection.cursor()
        cursor.execute("INSERT INTO questions(q_type, q_title) VALUES (%s,%s)", (q.type , q.title));
        q_id = self.connection.insert_id();
        for i in range(len(q.answers)):
            self.save_answer(q.answers[i] , q_id);
        cursor.close();
        return q_id;

    @auto_reconnect
    def save_survey(self , s  , au_id ):
        cursor = self.connection.cursor();
        cursor.execute("INSERT INTO surveys(s_title, s_desc , au_id ) VALUES (%s,%s,%s)", (s.title , s.decription , au_id ));
        s_id = self.connection.insert_id();
        for i in range(len(s.questions)):
            q_id = self.save_question(s.questions[i])
            cursor.execute("INSERT INTO question_in_list(q_id, list_id , q_sort ) VALUES (%s,%s,%s)", (q_id , s_id , i ));
        cursor.close();
        self.connection.commit();
        return ;

    @auto_reconnect
    def get_survey_list(self , au_id , start , end):
        cursor = self.connection.cursor();
        cursor.execute("SELECT * FROM surveys WHERE au_id = %s" , (au_id));
        result = list(cursor.fetchall());
        result.sort(lambda x,y: cmp(x['au_id'], y['au_id']));
        result = sorted(result, key=lambda x:x['au_id']);
        returnlist = [];
        for i in range( end - start ):
            if ( len(result) <= start + i  ):
                return returnlist;
            s = question_list();
            s.title = result [ start + i ]["s_title"];
            s.description = "";
            s.id = result [ start + i ]["s_id"];
            s.owner = result [ start + i ]["au_id"];
            s.running = result [ start + i ]["s_running"];
            s.has_r = result [ start + i ]["s_answer"];
            returnlist.append( s );
        return returnlist;

    @auto_reconnect
    def stop_survey(self , s_id):
        cursor = self.connection.cursor();
        cursor.execute("SELECT * FROM surveys WHERE s_id = %s" , (s_id));
        s = cursor.fetchall();
        au_id = s[0]["au_id"];
        cursor.execute("UPDATE surveys SET s_running = %s WHERE s_id = %s", (0, s_id));
        cursor.execute("UPDATE appuser SET s_id = %s WHERE au_id = %s", (0, au_id));
        cursor.close();
        self.connection.commit();
        return;

    @auto_reconnect
    def start_survey(self , s_id):
        cursor = self.connection.cursor();
        cursor.execute("SELECT * FROM surveys WHERE s_id = %s" , (s_id));
        result = cursor.fetchall();
        if len( result ) != 1:
            return False;

        s = result[0];
        cursor.execute("SELECT * FROM appuser WHERE au_id = %s" , (s["au_id"]));
        user = cursor.fetchall();
        if len( user ) != 1 :
            return False;
        u = user[0];
        if u["s_id"] != 0:
            #有已经激活的问卷，停止他
            self.stop_survey( u["s_id"]);
        cursor.execute("UPDATE surveys SET s_running = %s WHERE s_id = %s", (1, s_id));
        cursor.execute("UPDATE appuser SET s_id = %s WHERE au_id = %s", (s_id, s["au_id"]));
        cursor.close();
        self.connection.commit();
        return True;

    @auto_reconnect
    def delete_survey(self , s_id):
        cursor = self.connection.cursor();

        cursor.execute("SELECT * FROM customer_answers WHERE s_id = %s" , (s_id));
        result = cursor.fetchall();
        for i in range(len(result)):
            ca_id = result[i]["ca_id"];
            cursor.execute("DELETE FROM customer_answer_details WHERE ca_id = %s" , (ca_id));

        cursor.execute("SELECT * FROM question_in_list WHERE list_id = %s" , (s_id));
        result = cursor.fetchall();
        for i in range(len(result)):
            q_id = result[i]["q_id"];
            cursor.execute("DELETE FROM answers WHERE q_id = %s" , (q_id));
            cursor.execute("DELETE FROM questions WHERE q_id = %s" , (q_id));

        cursor.execute("DELETE FROM customer_answers WHERE s_id = %s" , (s_id));
        cursor.execute("DELETE FROM question_in_list WHERE list_id = %s" , (s_id));
        cursor.execute("DELETE FROM surveys WHERE s_id = %s" , (s_id));
        cursor.close();
        self.connection.commit();
        return;

    @auto_reconnect
    def save_reply(self , ca ):
        cursor = self.connection.cursor();
        cursor.execute("INSERT INTO customer_answers( s_id , ct_id , ct_nick ) VALUES (%s ,%s , %s)", (ca.s_id , ca.ct_id , ca.ct_nick));
        ca_id = self.connection.insert_id()
        for i in range(len(ca.q_id_list)):
            string = ca.answer_list[i];
            id = ca.q_id_list[i];
            text = ca.input_list[i];
            cursor.execute("INSERT INTO customer_answer_details( ca_id , q_id , answer , input , ct_nick) VALUES (%s,%s,%s,%s,%s)", (ca_id , id , string , text , ca.ct_nick ));
        cursor.execute("UPDATE surveys SET s_answer = s_answer+1 WHERE s_id = %s", (ca.s_id));
        cursor.close();
        self.connection.commit();
        return;

    @auto_reconnect
    def get_reply(self, s_id):
        cursor = self.connection.cursor();
        cursor.execute("SELECT * FROM question_in_list WHERE list_id = %s" , (s_id));
        result = list(cursor.fetchall())
        result.sort(lambda x,y: cmp(x['q_sort'], y['q_sort']));
        result = sorted(result, key=lambda x:x['q_sort']);
        returnitem = survey_answer_report()
        returnitem.s_id = s_id
        al_names = {};
        for item in result:
            cursor.execute("SELECT * FROM customer_answer_details WHERE q_id = %s" , (item["q_id"]));
            answertoq = list(cursor.fetchall());
            returnitem.answer_list.append(answertoq);
        cursor.close();
        return returnitem;

    @auto_reconnect
    def get_app_user(self, visit_id, visit_nick, top_session):
        cursor = self.connection.cursor()
        cursor.execute("SELECT * FROM appuser WHERE t_id = %s AND t_nick = %s", (visit_id, visit_nick))
        appuser= cursor.fetchall()
        return_a = app_user();
        if len(appuser) == 0:
            cursor.execute("INSERT INTO appuser(t_id, t_nick, session) VALUES (%s, %s, %s)", (visit_id, visit_nick, top_session))
            return_a.id = self.connection.insert_id()
            return_a.s_id = 0
        else:
            return_a.id = appuser[0]['au_id']
            return_a.s_id = appuser[0]['s_id']
            cursor.execute("UPDATE appuser SET session = %s WHERE au_id = %s", (top_session, appuser[0]['au_id']))
        return_a.t_nick = visit_nick
        return_a.t_id = visit_id
        return_a.session = top_session
        self.connection.commit()
        cursor.close()
        return return_a;

    @auto_reconnect
    def get_seller_id(self , nick):
        cursor = self.connection.cursor();
        cursor.execute("SELECT * FROM appuser WHERE t_nick = %s", (nick))
        appuser= cursor.fetchall()
        cursor.close();
        return appuser[0]['au_id'];

    @auto_reconnect
    def get_survey_user_list(self , s_id):
        cursor = self.connection.cursor();
        cursor.execute("SELECT * FROM customer_answers WHERE s_id = %s", (s_id))
        users = cursor.fetchall()
        list = []
        for user in users:
            if user["ct_nick"] != "匿名":
                u = survey_user();
                u.nick = user["ct_nick"]
                cursor.execute("SELECT * FROM customer_answers WHERE ct_nick = %s", (u.nick))
                times = cursor.fetchall();
                u.num = len(times);
                list.append(u);
        cursor.close();
        return list;
"""
DB = Database()
